
-- Type used for returning tide measurements
CREATE TYPE elevation_measurement AS (
time timestamptz, elevation float
)


-- Call the tides web service to acqire tidal measurements
-- DROP function get_tides_unsafe(starttime text, endtime text, minutes int);
CREATE OR REPLACE FUNCTION get_tides_unsafe(starttime text, endtime text, minutes int)
RETURNS SETOF text AS
$BODY$
  import urllib
  st = urllib.quote(starttime)
  et = urllib.quote(endtime)  
  mn = urllib.quote(str(minutes)) 
  args = '?starttime=%s&endtime=%s&step_in_minutes=%s' % (st, et, mn)
  url = 'http://data.stccmop.org/ws/util/tide' + args
  stream = urllib.urlopen(url)
  ret = ''
  for line in stream.readlines():
    yield line
  
  return
$BODY$
  LANGUAGE 'plpythonu' IMMUTABLE;
  COMMENT ON FUNCTION get_tides_unsafe(starttime text, endtime text, minutes int) IS 'Access the web service to retrieve tide calculations.  Arguments 1 and 2 are strings formatted appropriately for the web service.  Argument 3 is the inter-measurement interval in minutes.  Unsafe to use this untyped version directly.  Use the tides(...) family of functions instead.';





-- DROP FUNCTION tides_unpaged(timestamp with time zone, timestamp with time zone, interval);
CREATE OR REPLACE FUNCTION tides_unpaged(starttime timestamp with time zone, endtime timestamp with time zone, width interval)
  RETURNS SETOF elevation_measurement AS
$BODY$
select (substring(get_tides_unsafe from '(.*),.*') || ' US/Pacific')::timestamptz as time, 
       substring(get_tides_unsafe from '.*,(.*)')::float as elevation
from get_tides_unsafe((SELECT to_char($1 AT TIME ZONE 'US/Pacific', 'YYYY-MM-DD HH24:MI')), 
                      (SELECT to_char($2 AT TIME ZONE 'US/Pacific', 'YYYY-MM-DD HH24:MI')),
                      (extract('epoch' from $3::interval)/60.0)::int)
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

--Base version: starttime, endtime, interval between measurements, and size of window per web service invocation
--DROP FUNCTION tides(starttime timestamptz, endtime timestamptz, width interval, pagesize interval);
CREATE OR REPLACE FUNCTION tides(starttime timestamptz, endtime timestamptz, width interval, pagesize interval)
  RETURNS SETOF elevation_measurement AS
$BODY$
DECLARE	
	r record;
	t record;
BEGIN
	FOR r IN select * from timerange($1, $2, $4) LOOP
		FOR t IN SELECT * FROM tides_unpaged(r.t1, r.t2, $3) LOOP
			RETURN NEXT t;	   
		END LOOP;
	END LOOP; 
	
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;
COMMENT ON FUNCTION tides(timestamp with time zone, timestamp with time zone, width interval, pagesize interval) IS 'Retrieve tidal elevations at between starttime and endtime at a frequency governed by the time interval given in the third argument.  The time interval fourth argument $4 is used to divide the time period into $4-size chunks.  The web service will be called once for each chunk.  This version of the function is used to avoid requesting years worth of data from the server.';

-- Overload with three arguments; page size fixed at 1 week
-- drop function tides(starttime timestamp with time zone, endtime timestamp with time zone, width interval);
CREATE OR REPLACE FUNCTION tides(starttime timestamp with time zone, endtime timestamp with time zone, width interval)
  RETURNS SETOF elevation_measurement AS
$BODY$
select * from tides($1, $2, $3, ($2 - $1)/((($2 - $1)/$3)/10000));
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

-- Overload with just two arguments; interval fixed at 5 minutes
--DROP FUNCTION tides(timestamp with time zone, timestamp with time zone);
CREATE OR REPLACE FUNCTION tides(starttime timestamp with time zone, endtime timestamp with time zone)
  RETURNS SETOF elevation_measurement AS
$BODY$
SELECT tides($1, $2, '5 minutes'::interval);
$BODY$
  LANGUAGE 'sql' IMMUTABLE;
  COMMENT ON FUNCTION tides(timestamp with time zone, timestamp with time zone) IS 'Retrieve tidal elevations at 5 minute frequency between starttime and endtime.';


-- Tide events

-- New type for a tidal event
CREATE TYPE tide_event AS
   ("time" timestamp with time zone, event text);


-- Call the tide web service to get events.  No checking of arguments or output
CREATE OR REPLACE FUNCTION get_tideevents_unsafe(starttime text, endtime text)
  RETURNS SETOF text AS
$BODY$
  import urllib
  st = urllib.quote(starttime)
  et = urllib.quote(endtime)  
  args = '?starttime=%s&endtime=%s' % (st, et)
  url = 'http://data.stccmop.org/ws/util/tideevents' + args
  stream = urllib.urlopen(url)
  ret = ''
  for line in stream.readlines():
    yield line.strip()
  return
$BODY$
  LANGUAGE 'plpythonu' IMMUTABLE;

-- Wrapper for the unsafe tide event call
CREATE OR REPLACE FUNCTION tideevents_unpaged(starttime timestamp with time zone, 
endtime timestamp with time zone)
  RETURNS SETOF elevation_measurement AS
$BODY$
select (substring(get_tides_unsafe from '(.*),.*') || ' US/Pacific')::timestamptz as time, 
       substring(get_tides_unsafe from '.*,(.*)')::float as elevation
from get_tides_unsafe((SELECT to_char($1 AT TIME ZONE 'US/Pacific', 'YYYY-MM-DD HH24:MI')), 
                      (SELECT to_char($2 AT TIME ZONE 'US/Pacific', 'YYYY-MM-DD HH24:MI')),
                      (extract('epoch' from $3::interval)/60.0)::int)
$BODY$
  LANGUAGE 'sql' IMMUTABLE;

-- Paged results.  Third argument is page size as a time interval
CREATE OR REPLACE FUNCTION tideevents(starttime timestamp with time zone, 
endtime timestamp with time zone, pagesize interval)
  RETURNS SETOF tide_event AS
$BODY$
DECLARE 
  r record;
  t record;
BEGIN
  FOR r IN select * from timerange($1, $2, $3) LOOP
    FOR t IN SELECT * FROM tideevents_unpaged(r.t1, r.t2) LOOP
      RETURN NEXT t;     
    END LOOP;
  END LOOP; 
  
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;

-- Assume a reasonable page size.  3 years ~ 8000 records
CREATE OR REPLACE FUNCTION tideevents(starttime timestamp with time zone, 
endtime timestamp with time zone)
  RETURNS SETOF tide_event AS
$BODY$
select * from tideevents($1, $2, '3 years');
$BODY$
  LANGUAGE 'sql' IMMUTABLE;


-- Tide intervals

CREATE TYPE tide_event_interval AS
   (starttime timestamp with time zone, 
    startevent text, 
    endtime timestamp with time zone, 
    endevent text
);


CREATE OR REPLACE FUNCTION tideevent_intervals(starttime timestamp with time zone, 
endtime timestamp with time zone, regex text)
  RETURNS SETOF tide_event_interval AS
$BODY$
DECLARE
    this record;
    previous record;
    output record;
    i int;
    starttime alias for $1;
    endtime alias for $2;
    regex alias for $3;
BEGIN

i = 0;
FOR this in select * from tideevents(starttime, endtime) where substring(event from regex) is not null LOOP
  if i > 0 THEN
    output = (previous.time, previous.event, this.time, this.event)::tide_event_interval;
    RETURN NEXT output;
  END IF;
  previous = this;
  i = i + 1;
END LOOP;

END
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;


-- Test the functions
SELECT * FROM tides('1/1/2008 PST', '1/1/2008 10:30 PST');
SELECT * FROM tides('1/1/2008 PST', '1/1/2008 10:30 PST', '30 minutes');
SELECT * FROM tides('1/1/2008 PST', '1/1/2008 10:30 PST', '30 minutes', '6 hours');

SELECT * FROM tideevents('1/1/2008 PST', '2/1/2008 10:30 PST');
